package com.infodms.dms.dao.sales.marketmanage.planmanage;

import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.common.Constant;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.dao.sales.planmanage.SpacePlanReportDao;
import com.infodms.dms.util.CommonUtils;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

public class ActivitiesMediaCostDao  extends BaseDao{
	public static Logger logger = Logger.getLogger(SpacePlanReportDao.class);
	private static final SpacePlanReportDao dao = new SpacePlanReportDao ();
	public static final SpacePlanReportDao getInstance() {
		return dao;
	}
	
	public static Map<String, Object> meidaCostQuery(String costType, String dutyType, String pageOrgId, String orgId, String startTime, String endTime, String areaId, String region, String model, String mediaType, String poseId, String mediaName, int pageSize, int curPage) {
		boolean orgFlag = Constant.DUTY_TYPE_LARGEREGION.toString().equals(dutyType) || (!CommonUtils.isNullString(pageOrgId) && Constant.DUTY_TYPE_COMPANY.toString().equals(dutyType) && !orgId.toString().equals(pageOrgId)) ;
		
		StringBuffer sql = new StringBuffer("\n") ;
		
		sql.append("SELECT DISTINCT TCMC.COST_ID,\n");
		sql.append("       TCMC.EXECUTE_ID,\n");  
		sql.append("       TCMC.COST_TYPE,\n");  
		sql.append("       TCMC.ADV_SUBJECT,\n");  
		sql.append("       TO_CHAR(TCMC.ADV_DATE,'YYYY-MM-DD') ADV_DATE,\n");  
		sql.append("       TO_CHAR(TCMC.END_DATE,'YYYY-MM-DD') END_DATE,\n");  
		sql.append("       TCMC.PAYMENT_ACCOUNT,\n");  
		sql.append("       NVL(TCMC.ITEM_PRICE, 0) ITEM_PRICE,\n");  
		sql.append("       NVL(TCMC.ITEM_COUNT, 0) ITEM_COUNT,\n");  
		sql.append("       NVL(TCMC.PLAN_COST, 0) PLAN_COST,\n");  
		sql.append("       NVL(TCMC.REAL_COST, 0) REAL_COST,\n");  
		sql.append("       NVL(TCMC.ITEM_COST, 0) ITEM_COST,\n");  
		sql.append("       TCMC.MEDIA_TYPE,\n");  
		sql.append("       TCMC.REGION,\n");  
		sql.append("       TMR.REGION_NAME,\n");  
		sql.append("       TCMC.MEDIA_MODEL,\n");  
		sql.append("       TCMC.MEDIA_NAME,\n");  
		sql.append("       TCMC.MEDIA_PUBLISH,\n");  
		sql.append("       TCMC.MEDIA_SIZE,\n");  
		sql.append("       TCMC.MEDIA_COLUMN,\n");  
		sql.append("       TDBA.AREA_NAME,\n");  
		sql.append("       TCMC.CREATE_DATE,\n");  
		sql.append("       DECODE(TC.CAMPAIGN_TYPE,").append(Constant.CAMPAIGN_TYPE_03).append(", TMO2.ORG_NAME, TMO1.ORG_NAME) ORG_NAME,\n");  
		sql.append("       TC.CAMPAIGN_TYPE,\n"); 
		sql.append("       NVL(TCMC.TOTAL_COUNT, 0) TOTAL_COUNT\n");  
		sql.append("  FROM TT_CAM_MEDIA_COST TCMC, TT_CAMPAIGN_EXECUTE TCE, TT_CAMPAIGN_SPACE_EXECUTE TCSE, TT_CAMPAIGN TC, TM_BUSINESS_AREA TDBA, TM_POSE_BUSINESS_AREA TPBA, TM_REGION TMR, TM_ORG TMO1, TM_ORG TMO2\n");  
		sql.append(" WHERE 1 = 1\n");  
		sql.append("   AND TC.AREA_ID = TDBA.AREA_ID\n");  
		sql.append("   AND TC.AREA_ID = TPBA.AREA_ID\n");
		sql.append("   AND TCMC.REGION = TMR.REGION_ID(+)\n");
		sql.append("   AND TCE.ORG_ID = TMO1.ORG_ID\n");
		sql.append("   AND TCSE.ORG_ID = TMO2.ORG_ID\n");
		
		sql.append("AND ((TCMC.EXECUTE_ID = TCE.EXECUTE_ID AND\n");
		sql.append("       TCE.CAMPAIGN_ID = TC.CAMPAIGN_ID AND\n");  
		sql.append("       TCE.CHECK_STATUS IN (").append(Constant.CAMPAIGN_CHECK_STATUS_06).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_07).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_08).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_09).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_10).append(") AND\n");  
		sql.append("       TC.CAMPAIGN_TYPE IN (").append(Constant.CAMPAIGN_TYPE_01).append(", ").append(Constant.CAMPAIGN_TYPE_02).append(")") ;
		if(orgFlag) {
			if(!CommonUtils.isNullString(pageOrgId)) {
				orgId = pageOrgId ;
			}
			
			sql.append(" AND TCE.ORG_ID = ").append(orgId).append("\n");
		}
		sql.append(") OR\n");  
		sql.append("       (TCMC.EXECUTE_ID = TCSE.SPACE_ID AND\n");  
		sql.append("       TCSE.CAMPAIGN_ID = TC.CAMPAIGN_ID AND\n");  
		sql.append("       TCSE.CHECK_STATUS IN (").append(Constant.CAMPAIGN_CHECK_STATUS_06).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_07).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_08).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_09).append(",").append(Constant.CAMPAIGN_CHECK_STATUS_10).append(") AND\n");  
		sql.append("       TC.CAMPAIGN_TYPE = ").append(Constant.CAMPAIGN_TYPE_03) ;
		if(orgFlag) {
			if(!CommonUtils.isNullString(pageOrgId)) {
				orgId = pageOrgId ;
			}
			
			sql.append(" AND TCSE.ORG_ID = ").append(orgId).append("\n");
		}
		sql.append("))\n");

		sql.append("   AND TPBA.POSE_ID = ").append(poseId).append("\n");
		
		if(!CommonUtils.isNullString(startTime)) {
			sql.append("   AND TO_CHAR(TCMC.ADV_DATE,'YYYY-MM-DD') >= '").append(startTime).append("'\n");
		}
		
		if(!CommonUtils.isNullString(endTime)) {
			sql.append("   AND TO_CHAR(TCMC.END_DATE,'YYYY-MM-DD') <= '").append(endTime).append("'\n");
		}
		
		if(!CommonUtils.isNullString(areaId)) {
			sql.append("   AND TC.AREA_ID = ").append(areaId).append("\n");
		}
		
		if(!CommonUtils.isNullString(region)) {
			sql.append("   AND TCMC.REGION = ").append(region).append("\n");
		}
		
		if(!CommonUtils.isNullString(model)) {
			sql.append("   AND TCMC.MEDIA_MODEL = ").append(model).append("\n");
		}
		
		if(!CommonUtils.isNullString(mediaType)) {
			sql.append("   AND TCMC.MEDIA_TYPE = ").append(mediaType).append("\n");
		}
		
		if(!CommonUtils.isNullString(costType)) {
			sql.append("   AND TCMC.COST_TYPE = ").append(costType).append("\n");
		}
		
		if(!CommonUtils.isNullString(mediaName)) {
			sql.append("   AND TCMC.MEDIA_NAME LIKE '%").append(mediaName).append("%'\n");
		}
		
		sql.append("ORDER BY TCMC.CREATE_DATE DESC, TCMC.EXECUTE_ID") ;
		
		Map<String, Object> mediaCostMap = new HashMap<String, Object>() ;
		
		List<Map<String, Object>> mediaCostList = dao.pageQuery(sql.toString(), null, dao.getFunName()) ;
		
		PageResult<Map<String, Object>> mediaCostResult = dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize, curPage) ;
		
		mediaCostMap.put("mediaCostList", mediaCostList) ;
		mediaCostMap.put("mediaCostResult", mediaCostResult) ;
		
		return mediaCostMap ;
	}
	
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
}
